#!/usr/bin/perl
use strict;
use warnings;

use Test::More 'no_plan'; # currently no_plan

BEGIN {
       use_ok( 'MySQL::Query' );
}

my @sql = (
       "SELECT rowxx1 FROM table1",
       "SELECT COUNT(*) FROM table1",
       "SELECT COUNT(col1) FROM table1",
       "SELECT CONCAT(col1 , col2) AS col3 FROM table1",
       "SELECT CONCAT(t1.col1 , t2.col2) FROM t1 JOIN t2",
       "SELECT CONCAT(t1.col1 , t2.col2) AS al FROM t1 JOIN t2 ON t1.id = t2.id",
       "SELECT CONCAT(t1.col1 , t2.col2) AS ty , COUNT(t2.col3) FROM t1, t2",
       "SELECT col1 , col2 qw , col3 AS nm , tab.col4 , tab.col5 zx , tab.col6 AS er FROM tab", # diferent select tpyes
       "SELECT tab.col1 , fg.col2 , CEIL(tab.dgg) AS nmk , COUNT(fg.ddddd) FROM tab , fg", # simple functions with aliases
       "SELECT author_name FROM biblio_db.author_list",
       "SELECT col_name FROM tbl_name AS a WHERE a.col_name = 1 OR a.col_name2 = 2",
       "SELECT t1.name, t2.salary FROM employee AS t1, info AS t2 WHERE t1.name = t2.name",
       "SELECT t1.name, t2.salary FROM employee t1, info t2 WHERE t1.name = t2.name",
       "SELECT college, region, seed FROM tournament ORDER BY region, seed",
       "SELECT college, region AS r, seed AS s FROM tournament ORDER BY r, s",
       "SELECT college, region, seed FROM tournament ORDER BY 2, 3",
       "SELECT a, COUNT(b) FROM test_table GROUP BY a ORDER BY NULL",
       "SELECT user, MAX(salary) FROM users GROUP BY user",
       "SELECT 12 AS a, a FROM t GROUP BY a",
       "SELECT name , surname , COUNT(name) AS cnt_n , COUNT(surname) AS cnt_s FROM the_table GROUP BY name",
       "SELECT t1.name, t2.salary FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name",
       "SELECT t1.name, t2.salary FROM employee t1 INNER JOIN info t2 ON t1.name = t2.name",
       "SELECT left_tbl.* FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id WHERE right_tbl.id IS NULL",
       "SELECT * FROM table1, table2",
       "SELECT * FROM table1 INNER JOIN table2 ON table1.id=table2.id",
       "SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id",
       #"SELECT * FROM table1 LEFT JOIN table2 USING (id)",
       "SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id LEFT JOIN table3 ON table2.id=table3.id",
       "SELECT * FROM t1 LEFT JOIN t2 ON (t1.a = t2.a)",
       "SELECT * FROM t1 RIGHT JOIN t2 ON (t1.a = t2.a)",
       'SELECT names.codename, s1.score AS "Score1", s1.comment AS "Comments1", s2.score AS "Score2", s2.comment AS "Comments2", SUM(st.score) AS "Total" FROM students names LEFT JOIN scores s1 ON s1.act_id=1 AND names.id=s1.student_id LEFT JOIN scores s2 ON s2.act_id=2 AND names.id=s2.student_id LEFT JOIN scores st ON names.id=st.student_id WHERE names.codename <> "" GROUP BY names.codename ORDER BY names.codename',
       'select t1.mygroup, sum(t2.value) as cond_1, sum(t3.value) as cond_2, sum(t4.value) as cond_3 from testtable t1 left join testtable t2 on t1.mykey=t2.mykey left join testtable t3 on t1.mykey=t3.mykey left join testtable t4 on t1.mykey=t4.mykey where t2.cond=1 and t3.cond=2 and t4.cond=3',
       'select t1.mygroup, sum(t2.value) as cond_1, sum(t3.value) as cond_2, sum(t4.value) as cond_3 from testtable t1 left join testtable t2 on t1.mykey=t2.mykey and t2.cond=1 left join testtable t3 on t1.mykey=t3.mykey and t3.cond=2 left join testtable t4 on t1.mykey=t4.mykey and t4.cond=3',
       'SELECT table1.id FROM table1 LEFT JOIN table2 ON table1.id = table2.id LEFT JOIN table3 ON table1.id2 = table3.id2 LEFT JOIN table4 ON table1.id3 = table4.id3',

q{
       SELECT
    CONCAT(p1.id, p1.tvab) AS tvid,
    CONCAT(p1.christian_name, ' ', p1.surname) AS Nameq,
    p1.postal_code AS Code,
    p1.city AS City,
    pg.abrev AS Area,
    p1.dead AS dead1,
    l.event AS event1,
    td.suspect AS tsuspect1,
    id.suspect AS isuspect1,
    td.severe AS tsevere1,
    id.severe AS isevere1,
    p2.dead AS dead2,
    l2.event AS event2,
    h2.nurse AS nurse2,
    h2.doctor AS doctor2,
    td2.suspect AS tsuspect2,
    id2.suspect AS isuspect2,
    td2.severe AS tsevere2,
    id2.severe AS isevere2,
    l.finish_date
FROM
    twin_project AS tp
    /* For Twin 1 */
    LEFT JOIN twin_data AS td ON tp.id = td.id
              AND tp.tvab = td.tvab
    LEFT JOIN informant_data AS id ON tp.id = id.id
              AND tp.tvab = id.tvab
    LEFT JOIN harmony AS h ON tp.id = h.id
              AND tp.tvab = h.tvab
    LEFT JOIN lentus AS l ON tp.id = l.id
              AND tp.tvab = l.tvab
    /* For Twin 2 */
    LEFT JOIN twin_data AS td2 ON p2.id = td2.id
              AND p2.tvab = td2.tvab
    LEFT JOIN informant_data AS id2 ON p2.id = id2.id
              AND p2.tvab = id2.tvab
    LEFT JOIN harmony AS h2 ON p2.id = h2.id
              AND p2.tvab = h2.tvab
    LEFT JOIN lentus AS l2 ON p2.id = l2.id
              AND p2.tvab = l2.tvab,
    person_data AS p1,
    person_data AS p2,
    postal_groups AS pg
WHERE
    p1.id = tp.id AND p1.tvab = tp.tvab AND
    p2.id = p1.id AND p2.ptvab = p1.tvab AND
    tp.survey_no = 5 AND
    (p2.dead = 0 OR p2.dead = 9 OR (p2.dead = 1 AND p2.death_date = 0))
    AND
    (
     (td.future_contact = 'Yes' AND td.suspect = 2) OR
     (td.future_contact = 'Yes' AND td.suspect = 1 AND id.suspect = 1) OR
     (td.suspect IS NULL AND id.suspect = 1 AND id.future_contact = 'Yes') OR
     (td.participation = 'Aborted' AND id.suspect = 1 AND id.future_contact = 'Yes') OR
     (td.participation = 'Aborted' AND id.suspect IS NULL AND p2.dead = 0)
    )
    AND l.event = 'Finished'
    AND (h.nurse IS NULL OR h.nurse=00 OR h.doctor=00)
    AND NOT (h.status = 'Refused' OR h.status = 'Aborted' OR h.status = 'Died' OR h.status = 'Other')
ORDER BY
    tvid
    
},
       "SELECT CONCAT(last_name,', ',first_name) AS full_name FROM mytable ORDER BY full_name",
       
);



foreach (@sql) {
       my $tree = MySQL::Query->new( sql => $_ );
       ok( ref $tree->as_builder eq 'SQL::Builder::Select');
}